#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   sql.py
@Time    :   2024/01/23 17:30:08
@Author  :   chakcy 
@Email   :   947105045@qq.com
@description   :   数据库
'''
from rpa_tool.data_table.data_table import DataTable
from rpa_tool.data_table.write_range import write_to_excel_range

import sqlite3

# sqlite 数据库类
class DataBase():
    # 初始化
    def __init__(self, db_path:str, columns:list, table_name:str):
        self.db_path = db_path
        self.conn = sqlite3.connect(db_path)
        self.cursor = self.conn.cursor()
        self.columns = columns
        self.table_name = table_name
        self.create_table()

    # 创建数据库
    def create_table(self):
        create_table_query = f"""
        CREATE TABLE IF NOT EXISTS {self.table_name}
        (
            id INTEGER PRIMARY KEY,
            {', '.join(self.columns)},
            date timestamp not null default (datetime('now', 'localtime'))
        );
        """
        print(create_table_query)
        self.cursor.execute(create_table_query)
        self.conn.commit()

    # 添加数据
    def insert_data(self, data:list):
        placeholders = ', '.join(['?' for _ in range(len(self.columns))])
        insert_query = f"INSERT INTO {self.table_name} ({','.join([column.split(' ')[0] for column in self.columns])}) VALUES ({placeholders})"
        self.cursor.executemany(insert_query, data)  # 使用data作为参数执行查询  
        self.conn.commit()

    # 导出excel
    def export_to_excel(self, sql:str, file_path:str, sheet_name:str, range:str):
        data = DataTable(file_path=self.db_path, sql=sql).get_data_table()
        write_to_excel_range(file_path, sheet_name, data, f"{range}{len(data)}")


# 数据转换
def data_conversion(data:list):
    return [tuple(d.values()) for d in data]

if __name__ == "__main__":
    db = DataBase('example.db', ['name varchar(50) not null', 'age INTEGER'], 'users')
    db.insert_data([('Alice', 25), ('Bob', 30)])
